# -*- coding: utf-8 -*-

# Define here the models for your scraped items
#
# See documentation in:
# https://doc.scrapy.org/en/latest/topics/items.html

import scrapy


class QiutanItem(scrapy.Item):
    # define the fields for your item here like:
    # name = scrapy.Field()
    pass


class SaichengItem(scrapy.Item):
    # define the fields for your item here like:
    # name = scrapy.Field()
    league = scrapy.Field()
    season = scrapy.Field()
    lunci = scrapy.Field()
    bs_num_id = scrapy.Field()
    FTR = scrapy.Field()
    FTRR = scrapy.Field()
    bs_time = scrapy.Field()
    hometeam = scrapy.Field()
    h_team_id = scrapy.Field()
    res_score = scrapy.Field()
    FTHG= scrapy.Field()
    FTAG = scrapy.Field()
    awayteam = scrapy.Field()
    a_team_id = scrapy.Field()
    all_rang = scrapy.Field()
    half_rang = scrapy.Field()
    sizes_balls_a = scrapy.Field()
    sizes_balls_h = scrapy.Field()
    half_score = scrapy.Field()

    def get_insert_data(self):
        insert_sql = 'INSERT IGNORE INTO all_bs_data values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        data = (
        self['league'], self['season'], self['lunci'], self['bs_num_id'], self['FTR'], self['FTRR'], self['bs_time'], self['hometeam'],
        self['h_team_id'], self['res_score'], self['FTHG'], self['FTAG'], self['awayteam'], self['a_team_id'], self['all_rang'],
        self['half_rang'], self['sizes_balls_a'], self['sizes_balls_h'], self['half_score'])
        return insert_sql, data


# all_bs_data 建表语句
# CREATE TABLE all_bs_data(
# league VARCHAR(20),
# season VARCHAR(20),
# lunci TINYINT,
# bs_num_id INT,
# FTR VARCHAR(10),
# FTRR VARCHAR(10),
# bs_time VARCHAR(20),
# hometeam VARCHAR(20),
# h_team_id VARCHAR(6),
# res_score VARCHAR(10),
# FTHG INT,
# FTAG INT,
# awayteam VARCHAR(20),
# a_team_id VARCHAR(6),
# all_rang VARCHAR(6),
# half_rang VARCHAR(6),
# sizes_balls_a VARCHAR(6),
# sizes_balls_h VARCHAR(6),
# half_score VARCHAR(6),
# PRIMARY KEY(bs_num_id)
# )DEFAULT CHARSET=utf8mb4;
# alter table all_bs_data add bs_num_id int after lunci;


class Team_DataItem(scrapy.Item):
    # define the fields for your item here like:
    team_id = scrapy.Field()
    team_name = scrapy.Field()
    Eng_name = scrapy.Field()
    team_city = scrapy.Field()
    team_home = scrapy.Field()
    build_team_time = scrapy.Field()
    var_coach = scrapy.Field()
    team_youshi = scrapy.Field()
    team_style = scrapy.Field()
    team_ruodian = scrapy.Field()
    team_stats = scrapy.Field()

    def get_insert_data(self):
        insert_sql = 'INSERT IGNORE INTO all_team_data(team_id,team_name,Eng_name,team_city,team_home,build_team_time,var_coach,team_youshi,team_style,team_ruodian,team_stats)values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        data = (self['team_id'], self['team_name'], self['Eng_name'], self['team_city'], self['team_home'],
                self['build_team_time'],
                self['var_coach'], self['team_youshi'], self['team_style'], self['team_ruodian'], self['team_stats'])

        return insert_sql, data


# CREATE TABLE all_team_data(id INT PRIMARY KEY AUTO_INCREMENT,
# team_id INT,
# team_name VARCHAR(20),
# Eng_name VARCHAR(30),
# team_city VARCHAR(30),
# team_home VARCHAR(30),
# build_team_time VARCHAR(20),
# var_coach VARCHAR(20),
# team_youshi VARCHAR(200),
# team_style VARCHAR(200),
# team_ruodian VARCHAR(200),
# team_stats VARCHAR(300)
# )DEFAULT CHARSET=utf8mb4;


class Match_Score_New_Item(scrapy.Item):
    # define the fields for your item here like:
    league = scrapy.Field()
    season = scrapy.Field()
    bs_num_id = scrapy.Field()
    lunci = scrapy.Field()
    hometeam = scrapy.Field()
    awayteam = scrapy.Field()
    bs_time = scrapy.Field()
    FTR = scrapy.Field()
    FTRR = scrapy.Field()
    res_score = scrapy.Field()
    FTHG = scrapy.Field()
    FTAG = scrapy.Field()
    h_nb_wins = scrapy.Field()
    h_nb_draws = scrapy.Field()
    h_nb_losts = scrapy.Field()
    HTGS = scrapy.Field()
    HTGC = scrapy.Field()
    HTGD = scrapy.Field()
    HTP = scrapy.Field()
    HLP = scrapy.Field()
    hh_nb_games = scrapy.Field()
    hh_nb_wins = scrapy.Field()
    hh_nb_draws = scrapy.Field()
    hh_nb_losts = scrapy.Field()
    HHTGS = scrapy.Field()
    HHTGC = scrapy.Field()
    HHTGD = scrapy.Field()
    HHTP = scrapy.Field()
    HHLP = scrapy.Field()

    a_nb_wins = scrapy.Field()
    a_nb_draws = scrapy.Field()
    a_nb_losts = scrapy.Field()
    ATGS = scrapy.Field()
    ATGC = scrapy.Field()
    ATGD = scrapy.Field()
    ATP = scrapy.Field()
    ALP = scrapy.Field()
    aa_nb_games = scrapy.Field()
    aa_nb_wins = scrapy.Field()
    aa_nb_draws = scrapy.Field()
    aa_nb_losts = scrapy.Field()
    AATGS = scrapy.Field()
    AATGC = scrapy.Field()
    AATGD = scrapy.Field()
    AATP = scrapy.Field()
    AALP = scrapy.Field()

    VTFormPtsStr = scrapy.Field()
    HTFormPtsStr = scrapy.Field()
    ATFormPtsStr = scrapy.Field()


    def get_insert_data(self):
        insert_sql = 'REPLACE INTO all_match_score values (' \
                     '%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s)'
        data = (
        self['league'], self['season'], self['bs_num_id'], self['lunci'], self['hometeam'], self['awayteam'], self['bs_time'], self['FTR'], self['FTRR'], self['res_score'],
        self['FTHG'], self['FTAG'], self['h_nb_wins'], self['h_nb_draws'], self['h_nb_losts'], self['HTGS'],  self['HTGC'], self['HTGD'], self['HTP'], self['HLP'],
        self['hh_nb_games'], self['hh_nb_wins'], self['hh_nb_draws'], self['hh_nb_losts'], self['HHTGS'], self['HHTGC'], self['HHTGD'], self['HHTP'], self['HHLP'],
        self['a_nb_wins'], self['a_nb_draws'], self['a_nb_losts'], self['ATGS'], self['ATGC'],self['ATGD'], self['ATP'], self['ALP'],
        self['aa_nb_games'], self['aa_nb_wins'], self['aa_nb_draws'], self['aa_nb_losts'], self['AATGS'], self['AATGC'], self['AATGD'], self['AATP'], self['AALP'],
        self['VTFormPtsStr'], self['HTFormPtsStr'], self['ATFormPtsStr'])

        return insert_sql, data

# CREATE TABLE all_match_score(
# league VARCHAR(20),
# season VARCHAR(20),
# bs_num_id INT,
# lunci TINYINT,
# hometeam VARCHAR(20),
# awayteam VARCHAR(20),
# bs_time VARCHAR(30),
# FTR VARCHAR(10),
# FTRR VARCHAR(10),
# res_score VARCHAR(20),
# FTHG INT,
# FTAG INT,
# h_nb_wins INT,
# h_nb_draws INT,
# h_nb_losts INT,
# HTGS INT,
# HTGC INT,
# HTGD INT,
# HTP INT,
# HLP INT,
# hh_nb_games INT,
# hh_nb_wins INT,
# hh_nb_draws INT,
# hh_nb_losts INT,
# HHTGS INT,
# HHTGC INT,
# HHTGD INT,
# HHTP INT,
# HHLP INT,
# a_nb_wins INT,
# a_nb_draws INT,
# a_nb_losts INT,
# ATGS INT,
# ATGC INT,
# ATGD INT,
# ATP INT,
# ALP INT,
# aa_nb_games INT,
# aa_nb_wins INT,
# aa_nb_draws INT,
# aa_nb_losts INT,
# AATGS INT,
# AATGC INT,
# AATGD INT,
# AATP INT,
# AALP INT,
# VTFormPtsStr VARCHAR(20),
# HTFormPtsStr VARCHAR(20),
# ATFormPtsStr VARCHAR(20),
# PRIMARY KEY(bs_num_id)
# )DEFAULT CHARSET=utf8mb4;


class Match_OZ_Odds_New_Item(scrapy.Item):
    # define the fields for your item here like:
    league = scrapy.Field()
    season = scrapy.Field()
    bs_num_id = scrapy.Field()
    hometeam = scrapy.Field()
    awayteam = scrapy.Field()
    oz_home0_mean = scrapy.Field()
    oz_draw0_mean = scrapy.Field()
    oz_away0_mean = scrapy.Field()
    oz_home9_mean = scrapy.Field()
    oz_draw9_mean = scrapy.Field()
    oz_away9_mean = scrapy.Field()
    oz_home0_std = scrapy.Field()
    oz_draw0_std = scrapy.Field()
    oz_away0_std = scrapy.Field()
    oz_home9_std = scrapy.Field()
    oz_draw9_std = scrapy.Field()
    oz_away9_std = scrapy.Field()


    def get_insert_data(self):
        insert_sql = 'REPLACE INTO all_match_oz_odds values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        data = (
        self['league'], self['season'], self['bs_num_id'],self['hometeam'], self['awayteam'],
        self['oz_home0_mean'], self['oz_draw0_mean'], self['oz_away0_mean'], self['oz_home9_mean'], self['oz_draw9_mean'], self['oz_away9_mean'],
        self['oz_home0_std'], self['oz_draw0_std'], self['oz_away0_std'], self['oz_home9_std'], self['oz_draw9_std'], self['oz_away9_std'])

        return insert_sql, data

# CREATE TABLE all_match_oz_odds(
# league VARCHAR(20),
# season VARCHAR(20),
# bs_num_id INT,
# oz_home0_mean FLOAT,
# oz_draw0_mean FLOAT,
# oz_away0_mean FLOAT,
# oz_home9_mean FLOAT,
# oz_draw9_mean FLOAT,
# oz_away9_mean FLOAT,
# oz_home0_std FLOAT,
# oz_draw0_std FLOAT,
# oz_away0_std FLOAT,
# oz_home9_std FLOAT,
# oz_draw9_std FLOAT,
# oz_away9_std FLOAT,
# PRIMARY KEY(bs_num_id)
# )DEFAULT CHARSET=utf8mb4;


class Match_AZ_Odds_New_Item(scrapy.Item):
    # define the fields for your item here like:
    league = scrapy.Field()
    season = scrapy.Field()
    bs_num_id = scrapy.Field()
    az_home0_mean = scrapy.Field()
    az_size0_mean = scrapy.Field()
    az_away0_mean = scrapy.Field()
    az_home9_mean = scrapy.Field()
    az_size9_mean = scrapy.Field()
    az_away9_mean = scrapy.Field()
    az_home0_std = scrapy.Field()
    az_size0_std = scrapy.Field()
    az_away0_std = scrapy.Field()
    az_home9_std = scrapy.Field()
    az_size9_std = scrapy.Field()
    az_away9_std = scrapy.Field()
    az_value0 = scrapy.Field()
    az_value9 = scrapy.Field()


    def get_insert_data(self):
        insert_sql = 'REPLACE INTO all_match_az_odds values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        data = (
        self['league'], self['season'], self['bs_num_id'],
        self['az_home0_mean'], self['az_size0_mean'], self['az_away0_mean'], self['az_home9_mean'], self['az_size9_mean'], self['az_away9_mean'],
        self['az_home0_std'], self['az_size0_std'], self['az_away0_std'], self['az_home9_std'], self['az_size9_std'], self['az_away9_std'],
        self['az_value0'], self['az_value9'])

        return insert_sql, data

# CREATE TABLE all_match_az_odds(
# league VARCHAR(20),
# season VARCHAR(20),
# bs_num_id INT,
# az_home0_mean FLOAT,
# az_size0_mean FLOAT,
# az_away0_mean FLOAT,
# az_home9_mean FLOAT,
# az_size9_mean FLOAT,
# az_away9_mean FLOAT,
# az_home0_std FLOAT,
# az_size0_std FLOAT,
# az_away0_std FLOAT,
# az_home9_std FLOAT,
# az_size9_std FLOAT,
# az_away9_std FLOAT,
# az_value0 FLOAT,
# az_value9 FLOAT,
# PRIMARY KEY(bs_num_id)
# )DEFAULT CHARSET=utf8mb4;



class Match_Sample_New_Item(scrapy.Item):
    league = scrapy.Field()
    season = scrapy.Field()
    bs_num_id = scrapy.Field()
    lunci = scrapy.Field()
    hometeam = scrapy.Field()
    awayteam = scrapy.Field()
    bs_time = scrapy.Field()
    FTR = scrapy.Field()
    res_score = scrapy.Field()
    FTHG = scrapy.Field()
    FTAG = scrapy.Field()
    VTFormPtsStr = scrapy.Field()
    HTFormPtsStr = scrapy.Field()
    ATFormPtsStr = scrapy.Field()

    def get_insert_data(self):
        insert_sql = 'REPLACE INTO all_match_samples values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        data = (self['league'], self['season'], self['bs_num_id'], self['lunci'], self['hometeam'], self['awayteam'],
        self['bs_time'], self['FTR'], self['res_score'], self['FTHG'], self['FTAG'], self['VTFormPtsStr'], self['HTFormPtsStr'], self['ATFormPtsStr'])

        return insert_sql, data



# CREATE TABLE all_match_samples(
# league VARCHAR(20),
# season VARCHAR(20),
# bs_num_id INT,
# lunci TINYINT,
# hometeam VARCHAR(20),
# awayteam VARCHAR(20),
# bs_time VARCHAR(20),
# FTR VARCHAR(10),
# res_score VARCHAR(10),
# FTHG INT,
# FTAG INT,
# VTFormPtsStr VARCHAR(20),
# HTFormPtsStr VARCHAR(20),
# ATFormPtsStr VARCHAR(20),
# PRIMARY KEY(bs_num_id)
# )DEFAULT CHARSET=utf8mb4;



class Team_ELO_Item(scrapy.Item):
    date_id = scrapy.Field()
    date = scrapy.Field()
    league = scrapy.Field()
    teamName = scrapy.Field()
    elo = scrapy.Field()

    def get_insert_data(self):
        insert_sql = 'REPLACE INTO all_team_elo values (%s,%s,%s,%s,%s)'
        data = (self['date_id'], self['date'], self['league'], self['teamName'], self['elo'])

        return insert_sql, data

# CREATE TABLE all_team_elo(
# day_id VARCHAR(40),
# day VARCHAR(20),
# league VARCHAR(20),
# teamName VARCHAR(20),
# elo INT,
# PRIMARY KEY(day_id)
# )DEFAULT CHARSET=utf8mb4;

class Team_VALUE_Item(scrapy.Item):
    season_id = scrapy.Field()
    season = scrapy.Field()
    league = scrapy.Field()
    teamName = scrapy.Field()
    squad = scrapy.Field()
    avg_value = scrapy.Field()
    sum_value = scrapy.Field()

    def get_insert_data(self):
        insert_sql = 'REPLACE INTO all_team_value values (%s,%s,%s,%s,%s,%s,%s)'
        data = (self['season_id'], self['season'], self['league'], self['teamName'], self['squad'], self['avg_value'], self['sum_value'])

        return insert_sql, data




class Match_OZ_Odds_company_Item(scrapy.Item):
    # define the fields for your item here like:
    league = scrapy.Field()
    season = scrapy.Field()
    bs_num_id = scrapy.Field()
    bs_time = scrapy.Field()
    hometeam = scrapy.Field()
    awayteam = scrapy.Field()

    Bet365_s1 = scrapy.Field()
    Bet365_sX = scrapy.Field()
    Bet365_s2 = scrapy.Field()
    Bet365_e1 = scrapy.Field()
    Bet365_eX = scrapy.Field()
    Bet365_e2 = scrapy.Field()
    WilliamHill_s1 = scrapy.Field()
    WilliamHill_sX = scrapy.Field()
    WilliamHill_s2 = scrapy.Field()
    WilliamHill_e1 = scrapy.Field()
    WilliamHill_eX = scrapy.Field()
    WilliamHill_e2 = scrapy.Field()
    Ladbrokes_s1 = scrapy.Field()
    Ladbrokes_sX = scrapy.Field()
    Ladbrokes_s2 = scrapy.Field()
    Ladbrokes_e1 = scrapy.Field()
    Ladbrokes_eX = scrapy.Field()
    Ladbrokes_e2 = scrapy.Field()
    Bet_at_home_s1 = scrapy.Field()
    Bet_at_home_sX = scrapy.Field()
    Bet_at_home_s2 = scrapy.Field()
    Bet_at_home_e1 = scrapy.Field()
    Bet_at_home_eX = scrapy.Field()
    Bet_at_home_e2 = scrapy.Field()
    Vcbet_s1 = scrapy.Field()
    Vcbet_sX = scrapy.Field()
    Vcbet_s2 = scrapy.Field()
    Vcbet_e1 = scrapy.Field()
    Vcbet_eX = scrapy.Field()
    Vcbet_e2 = scrapy.Field()
    Easybets_s1 = scrapy.Field()
    Easybets_sX = scrapy.Field()
    Easybets_s2 = scrapy.Field()
    Easybets_e1 = scrapy.Field()
    Easybets_eX = scrapy.Field()
    Easybets_e2 = scrapy.Field()
    Interwetten_s1 = scrapy.Field()
    Interwetten_sX = scrapy.Field()
    Interwetten_s2 = scrapy.Field()
    Interwetten_e1 = scrapy.Field()
    Interwetten_eX = scrapy.Field()
    Interwetten_e2 = scrapy.Field()
    _10BET_s1 = scrapy.Field()
    _10BET_sX = scrapy.Field()
    _10BET_s2 = scrapy.Field()
    _10BET_e1 = scrapy.Field()
    _10BET_eX = scrapy.Field()
    _10BET_e2 = scrapy.Field()
    _12bet_s1 = scrapy.Field()
    _12bet_sX = scrapy.Field()
    _12bet_s2 = scrapy.Field()
    _12bet_e1 = scrapy.Field()
    _12bet_eX = scrapy.Field()
    _12bet_e2 = scrapy.Field()
    _18Bet_s1 = scrapy.Field()
    _18Bet_sX = scrapy.Field()
    _18Bet_s2 = scrapy.Field()
    _18Bet_e1 = scrapy.Field()
    _18Bet_eX = scrapy.Field()
    _18Bet_e2 = scrapy.Field()
    Bwin_s1 = scrapy.Field()
    Bwin_sX = scrapy.Field()
    Bwin_s2 = scrapy.Field()
    Bwin_e1 = scrapy.Field()
    Bwin_eX = scrapy.Field()
    Bwin_e2 = scrapy.Field()
    Crown_s1 = scrapy.Field()
    Crown_sX = scrapy.Field()
    Crown_s2 = scrapy.Field()
    Crown_e1 = scrapy.Field()
    Crown_eX = scrapy.Field()
    Crown_e2 = scrapy.Field()
    Betfair_s1 = scrapy.Field()
    Betfair_sX = scrapy.Field()
    Betfair_s2 = scrapy.Field()
    Betfair_e1 = scrapy.Field()
    Betfair_eX = scrapy.Field()
    Betfair_e2 = scrapy.Field()

    DATA_TYPE = "COMPANYS_OZ"

    def get_insert_data(self):
        insert_sql = 'REPLACE INTO all_match_company_oz_odds values (%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s )'
        data = (
        self['league'], self['season'], self['bs_num_id'], self['bs_time'], self['hometeam'], self['awayteam'],
        self['Bet365_s1'],self['Bet365_sX'],self['Bet365_s2'],self['Bet365_e1'],self['Bet365_eX'], self['Bet365_e2'],
        self['WilliamHill_s1'],self['WilliamHill_sX'],self['WilliamHill_s2'],self['WilliamHill_e1'],self['WilliamHill_eX'],self['WilliamHill_e2'],
        self['Ladbrokes_s1'],self['Ladbrokes_sX'],self['Ladbrokes_s2'],self['Ladbrokes_e1'], self['Ladbrokes_eX'],self['Ladbrokes_e2'],
        self['Bet_at_home_s1'],self['Bet_at_home_sX'],self['Bet_at_home_s2'],self['Bet_at_home_e1'],self['Bet_at_home_eX'],self['Bet_at_home_e2'],
        self['Vcbet_s1'],self['Vcbet_sX'],self['Vcbet_s2'],self['Vcbet_e1'],self['Vcbet_eX'],self['Vcbet_e2'],
        self['Easybets_s1'], self['Easybets_sX'],self['Easybets_s2'],self['Easybets_e1'],self['Easybets_eX'],self['Easybets_e2'],
        self['Interwetten_s1'],self['Interwetten_sX'],self['Interwetten_s2'],self['Interwetten_e1'],self['Interwetten_eX'], self['Interwetten_e2'],
        self['_10BET_s1'],self['_10BET_sX'],self['_10BET_s2'],self['_10BET_e1'],self['_10BET_eX'],self['_10BET_e2'],
        self['_12bet_s1'],self['_12bet_sX'], self['_12bet_s2'], self['_12bet_e1'],self['_12bet_eX'], self['_12bet_e2'],
        self['_18Bet_s1'], self['_18Bet_sX'],self['_18Bet_s2'],self['_18Bet_e1'],self['_18Bet_eX'],self['_18Bet_e2'],
        self['Bwin_s1'],self['Bwin_sX'],self['Bwin_s2'],self['Bwin_e1'],self['Bwin_eX'],self['Bwin_e2'],
        self['Crown_s1'], self['Crown_sX'],self['Crown_s2'],self['Crown_e1'],self['Crown_eX'],self['Crown_e2'],
        self['Betfair_s1'],self['Betfair_sX'],self['Betfair_s2'],self['Betfair_e1'],self['Betfair_eX'],self['Betfair_e2'])

        return insert_sql, data, self.DATA_TYPE



class Match_Time_Series_OZ_Odds_Item(scrapy.Item):
    # define the fields for your item here like:
    league = scrapy.Field()
    season = scrapy.Field()
    bs_num_id = scrapy.Field()
    company_id = scrapy.Field()
    bs_time = scrapy.Field()
    hometeam = scrapy.Field()
    awayteam = scrapy.Field()
    FTR = scrapy.Field()

    time1 = scrapy.Field()
    time2 = scrapy.Field()
    time3 = scrapy.Field()
    time4 = scrapy.Field()
    time5 = scrapy.Field()
    time6 = scrapy.Field()
    time7 = scrapy.Field()
    time8 = scrapy.Field()
    time9 = scrapy.Field()
    time10 = scrapy.Field()
    time11 = scrapy.Field()
    time12 = scrapy.Field()
    time13 = scrapy.Field()
    time14 = scrapy.Field()
    time15 = scrapy.Field()
    time16 = scrapy.Field()
    time17 = scrapy.Field()
    time18 = scrapy.Field()
    time19 = scrapy.Field()
    time20 = scrapy.Field()
    time21 = scrapy.Field()
    time22 = scrapy.Field()
    time23 = scrapy.Field()
    time24 = scrapy.Field()
    time25 = scrapy.Field()
    time26 = scrapy.Field()
    time27 = scrapy.Field()
    time28 = scrapy.Field()
    time29 = scrapy.Field()
    time30 = scrapy.Field()
    time31 = scrapy.Field()
    time32 = scrapy.Field()
    time33 = scrapy.Field()
    time34 = scrapy.Field()
    time35 = scrapy.Field()
    time36 = scrapy.Field()
    time37 = scrapy.Field()
    time38 = scrapy.Field()
    time39 = scrapy.Field()
    time40 = scrapy.Field()

    DATA_TYPE = "TIME_SERIES_OZ"

    def get_insert_data(self):
        insert_sql_past = 'REPLACE INTO all_match_time_series_oz_odds values (%s,%s,%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        insert_sql_today = 'REPLACE INTO today_match_time_series_oz_odds values (%s,%s,%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,' \
                     '%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'

        data = (
        self['league'], self['season'], self['bs_num_id'], self['company_id'], self['bs_time'], self['hometeam'], self['awayteam'], self['FTR'],
        self['time1'], self['time2'], self['time3'], self['time4'], self['time5'], self['time6'], self['time7'], self['time8'], self['time9'], self['time10'],
        self['time11'], self['time12'], self['time13'], self['time14'], self['time15'], self['time16'], self['time17'], self['time18'], self['time19'], self['time20'],
        self['time21'], self['time22'], self['time23'], self['time24'], self['time25'], self['time26'], self['time27'], self['time28'], self['time29'], self['time30'],
        self['time31'], self['time32'], self['time33'], self['time34'], self['time35'], self['time36'], self['time37'], self['time38'], self['time39'], self['time40'])

        if self['FTR'] == 'UNKNOWN':
            return insert_sql_today, data, self.DATA_TYPE
        else:
            return insert_sql_past, data, self.DATA_TYPE




#创建合并的新表语句
# union_sql = 'CREATE TABLE league_match_data SELECT score.league, score.season, score.bs_num_id, score.lunci, score.hometeam, score.awayteam, score.FTR, score.h_win, score.h_draw, score.h_lost, score.a_win, score.a_draw, score.a_lost, score.HTGS, score.ATGS, score.HTGC, score.ATGC, score.HTGD, score.ATGD, score.HTP, score.ATP, score.HomeLP, score.AwayLP, score.VTFormPtsStr, score.HTFormPtsStr, score.ATFormPtsStr,oz.oz_home0_mean, oz.oz_draw0_mean, oz.oz_away0_mean, oz.oz_home9_mean, oz.oz_draw9_mean, oz.oz_away9_mean, oz.oz_home0_std, oz.oz_draw0_std, oz.oz_away0_std, oz.oz_home9_std, oz.oz_draw9_std, oz.oz_away9_std, az.az_home0_mean, az.az_size0_mean, az.az_away0_mean, az.az_home9_mean, az.az_size9_mean, az.az_away9_mean, az.az_home0_std, az.az_size0_std, az.az_away0_std, az.az_home9_std, az.az_size9_std, az.az_away9_std, az.az_value0, az.az_value9 FROM all_match_score score JOIN  all_match_oz_odds oz  on score.bs_num_id=oz.bs_num_id JOIN all_match_az_odds az ON score.bs_num_id=az.bs_num_id ;'

# 创建合并表语句
# 'CREATE TABLE taday_matchs  SELECT sc.league, sc.season, sc.bs_num_id, sc.lunci, sc.hometeam, sc.awayteam, sc.bs_time, sc.FTR, sc.FTRR,sc.res_score, sc.FTHG, sc.FTAG, sc.h_nb_wins, sc.h_nb_draws, sc.h_nb_losts, sc.HTGS, sc.HTGC, sc.HTGD, sc.HTP, sc.HLP, sc.hh_nb_games, sc.hh_nb_wins, sc.hh_nb_draws, sc.hh_nb_losts, sc.HHTGS, sc.HHTGC, sc.HHTGD, sc.HHTP, sc.HHLP, sc.a_nb_wins, sc.a_nb_draws, sc.a_nb_losts, sc.ATGS, sc.ATGC, sc.ATGD, sc.ATP, sc.ALP, sc.aa_nb_games, sc.aa_nb_wins, sc.aa_nb_draws, sc.aa_nb_losts, sc.AATGS, sc.AATGC, sc.AATGD, sc.AATP, sc.AALP, sc.VTFormPtsStr, sc.HTFormPtsStr, sc.ATFormPtsStr, oz.oz_home0_mean, oz.oz_draw0_mean, oz.oz_away0_mean, oz.oz_home9_mean, oz.oz_draw9_mean, oz.oz_away9_mean, oz.oz_home0_std, oz.oz_draw0_std, oz.oz_away0_std, oz.oz_home9_std, oz.oz_draw9_std, oz.oz_away9_std, az.az_home0_mean, az.az_size0_mean, az.az_away0_mean, az.az_home9_mean, az.az_size9_mean, az.az_away9_mean, az.az_home0_std, az.az_size0_std, az.az_away0_std, az.az_home9_std, az.az_size9_std, az.az_away9_std, az.az_value0, az.az_value9 FROM all_match_score sc JOIN all_match_oz_odds oz ON sc.bs_num_id = oz.bs_num_id JOIN all_match_az_odds az ON sc.bs_num_id = az.bs_num_id ;'


# CREATE TABLE all_match_samples2 SELECT ms.league, ms.season, ms.bs_num_id, ms.lunci, ms.hometeam, ms.awayteam, ms.bs_time, ms.FTR, ms.res_score, ms.FTHG, ms.FTAG, oz.oz_home0_mean, oz.oz_draw0_mean, oz.oz_away0_mean, oz.oz_home9_mean, oz.oz_draw9_mean, oz.oz_away9_mean FROM all_match_samples ms JOIN all_match_oz_odds oz ON ms.bs_num_id = oz.bs_num_id order by bs_time;
# ALTER TABLE all_match_samples2 ADD PRIMARY KEY(bs_num_id);


# insert into all_team_elo_2 (date_id,date,league,teamName,elo,team_name_cn) SELECT te.date_id,te.date,te.league,te.teamName,te.elo,tn.team_name_cn FROM all_team_elo te JOIN all_team_name tn ON te.teamName = tn.team_name_en;


# CREATE TABLE all_match_oz_odds2(league VARCHAR(20),season VARCHAR(20),bs_num_id INT,Bet365_s1 FLOAT, Bet365_sX FLOAT, Bet365_s2 FLOAT, Bet365_e1 FLOAT, Bet365_eX FLOAT, Bet365_e2 FLOAT,WilliamHill_s1 FLOAT, WilliamHill_sX FLOAT, WilliamHill_s2 FLOAT, WilliamHill_e1 FLOAT, WilliamHill_eX FLOAT, WilliamHill_e2 FLOAT,Ladbrokes_s1 FLOAT, Ladbrokes_sX FLOAT, Ladbrokes_s2 FLOAT, Ladbrokes_e1 FLOAT, Ladbrokes_eX FLOAT, Ladbrokes_e2 FLOAT,Bet_at_home_s1 FLOAT, Bet_at_home_sX FLOAT, Bet_at_home_s2 FLOAT, Bet_at_home_e1 FLOAT, Bet_at_home_eX FLOAT, Bet_at_home_e2 FLOAT,Vcbet_s1 FLOAT, Vcbet_sX FLOAT, Vcbet_s2 FLOAT, Vcbet_e1 FLOAT, Vcbet_eX FLOAT, Vcbet_e2 FLOAT,Easybets_s1 FLOAT, Easybets_sX FLOAT, Easybets_s2 FLOAT, Easybets_e1 FLOAT, Easybets_eX FLOAT, Easybets_e2 FLOAT,Interwetten_s1 FLOAT, Interwetten_sX FLOAT, Interwetten_s2 FLOAT, Interwetten_e1 FLOAT, Interwetten_eX FLOAT, Interwetten_e2 FLOAT,10BET_s1 FLOAT, 10BET_sX FLOAT, 10BET_s2 FLOAT, 10BET_e1 FLOAT, 10BET_eX FLOAT, 10BET_e2 FLOAT,12bet_s1 FLOAT, 12bet_sX FLOAT, 12bet_s2 FLOAT, 12bet_e1 FLOAT, 12bet_eX FLOAT, 12bet_e2 FLOAT,18Bet_s1 FLOAT, 18Bet_sX FLOAT, 18Bet_s2 FLOAT, 18Bet_e1 FLOAT, 18Bet_eX FLOAT, 18Bet_e2 FLOAT,Bwin_s1 FLOAT, Bwin_sX FLOAT, Bwin_s2 FLOAT, Bwin_e1 FLOAT, Bwin_eX FLOAT, Bwin_e2 FLOAT,Coral_s1 FLOAT, Coral_sX FLOAT, Coral_s2 FLOAT, Coral_e1 FLOAT, Coral_eX FLOAT, Coral_e2 FLOAT,Crown_s1 FLOAT, Crown_sX FLOAT, Crown_s2 FLOAT, Crown_e1 FLOAT, Crown_eX FLOAT, Crown_e2 FLOAT,Expekt_s1 FLOAT, Expekt_sX FLOAT, Expekt_s2 FLOAT, Expekt_e1 FLOAT, Expekt_eX FLOAT, Expekt_e2 FLOAT,Nordicbet_s1 FLOAT, Nordicbet_sX FLOAT, Nordicbet_s2 FLOAT, Nordicbet_e1 FLOAT, Nordicbet_eX FLOAT, Nordicbet_e2 FLOAT,Pinnacle_s1 FLOAT, Pinnacle_sX FLOAT, Pinnacle_s2 FLOAT, Pinnacle_e1 FLOAT, Pinnacle_eX FLOAT, Pinnacle_e2 FLOAT,SNAI_s1 FLOAT, SNAI_sX FLOAT, SNAI_s2 FLOAT, SNAI_e1 FLOAT, SNAI_eX FLOAT, SNAI_e2 FLOAT,STS_s1 FLOAT, STS_sX FLOAT, STS_s2 FLOAT, STS_e1 FLOAT, STS_eX FLOAT, STS_e2 FLOAT,Macauslot_s1 FLOAT, Macauslot_sX FLOAT, Macauslot_s2 FLOAT, Macauslot_e1 FLOAT, Macauslot_eX FLOAT, Macauslot_e2 FLOAT,Sportingbet_s1 FLOAT, Sportingbet_sX FLOAT, Sportingbet_s2 FLOAT, Sportingbet_e1 FLOAT, Sportingbet_eX FLOAT, Sportingbet_e2 FLOAT,188bet_s1 FLOAT, 188bet_sX FLOAT, 188bet_s2 FLOAT, 188bet_e1 FLOAT, 188bet_eX FLOAT, 188bet_e2 FLOAT,Sbobet_s1 FLOAT, Sbobet_sX FLOAT, Sbobet_s2 FLOAT, Sbobet_e1 FLOAT, Sbobet_eX FLOAT, Sbobet_e2 FLOAT,Mansion88_s1 FLOAT, Mansion88_sX FLOAT, Mansion88_s2 FLOAT, Mansion88_e1 FLOAT, Mansion88_eX FLOAT, Mansion88_e2 FLOAT,Wewbet_s1 FLOAT, Wewbet_sX FLOAT, Wewbet_s2 FLOAT, Wewbet_e1 FLOAT, Wewbet_eX FLOAT, Wewbet_e2 FLOAT,Betfair_s1 FLOAT, Betfair_sX FLOAT, Betfair_s2 FLOAT, Betfair_e1 FLOAT, Betfair_eX FLOAT, Betfair_e2 FLOAT,Betsson_s1 FLOAT, Betsson_sX FLOAT, Betsson_s2 FLOAT, Betsson_e1 FLOAT, Betsson_eX FLOAT, Betsson_e2 FLOAT,Unibet_s1 FLOAT, Unibet_sX FLOAT, Unibet_s2 FLOAT, Unibet_e1 FLOAT, Unibet_eX FLOAT, Unibet_e2 FLOAT,10x10bet_s1 FLOAT, 10x10bet_sX FLOAT, 10x10bet_s2 FLOAT, 10x10bet_e1 FLOAT, 10x10bet_eX FLOAT, 10x10bet_e2 FLOAT,1xBet_s1 FLOAT, 1xBet_sX FLOAT, 1xBet_s2 FLOAT, 1xBet_e1 FLOAT, 1xBet_eX FLOAT, 1xBet_e2 FLOAT,1xStavka_s1 FLOAT, 1xStavka_sX FLOAT, 1xStavka_s2 FLOAT, 1xStavka_e1 FLOAT, 1xStavka_eX FLOAT, 1xStavka_e2 FLOAT,5Dimes_s1 FLOAT, 5Dimes_sX FLOAT, 5Dimes_s2 FLOAT, 5Dimes_e1 FLOAT, 5Dimes_eX FLOAT, 5Dimes_e2 FLOAT,888Sport_s1 FLOAT, 888Sport_sX FLOAT, 888Sport_s2 FLOAT, 888Sport_e1 FLOAT, 888Sport_eX FLOAT, 888Sport_e2 FLOAT,PRIMARY KEY(bs_num_id)) DEFAULT CHARSET=utf8mb4;

  # company_id = {'281':'Bet365', '115':'WilliamHill', '82':'Ladbrokes', '173':'Bet_at_home', '81':'Vcbet', '90':'Easybets', '104':'Interwetten',
  #                     '16':'10BET', '18':'12bet', '976':'18Bet', '255':'Bwin', '88':'Coral', '545':'Crown', '70':'Expekt', '4':'Nordicbet', '177':'Pinnacle',
  #                     '110':'SNAI','60':'STS','80':'Macauslot','422':'Sportingbet','499':'188bet','474':'Sbobet','517':'Mansion88','659':'Wewbet','2':'Betfair',
  #                     '665':'Betsson','386':'Unibet','1254':'10x10bet','1047':'1xBet','1259':'1xStavka','436':'5Dimes','616':'888Sport'}
  #
  #   company_str = ''
  #   for id in company_id:
  #       company_str = company_str + r'{} FLOAT, {} FLOAT, {} FLOAT, {} FLOAT, {} FLOAT, {} FLOAT,'.format(company_id[id]+'_s1', company_id[id]+'_sX', company_id[id]+'_s2',company_id[id]+'_e1', company_id[id]+'_eX', company_id[id]+'_e2')
  #
  #
  #   sql_str = r'CREATE TABLE all_match_oz_odds2(league VARCHAR(20),season VARCHAR(20),bs_num_id INT,' + company_str + r'PRIMARY KEY(bs_num_id)) DEFAULT CHARSET=utf8mb4;'

# league
# season
# bs_num_id
# Bet365_s1
# Bet365_sX
# Bet365_s2
# Bet365_e1
# Bet365_eX
# Bet365_e2
# WilliamHill_s1
# WilliamHill_sX
# WilliamHill_s2
# WilliamHill_e1
# WilliamHill_eX
# WilliamHill_e2
# Ladbrokes_s1
# Ladbrokes_sX
# Ladbrokes_s2
# Ladbrokes_e1
# Ladbrokes_eX
# Ladbrokes_e2
# Bet_at_home_s1
# Bet_at_home_sX
# Bet_at_home_s2
# Bet_at_home_e1
# Bet_at_home_eX
# Bet_at_home_e2
# Vcbet_s1
# Vcbet_sX
# Vcbet_s2
# Vcbet_e1
# Vcbet_eX
# Vcbet_e2
# Easybets_s1
# Easybets_sX
# Easybets_s2
# Easybets_e1
# Easybets_eX
# Easybets_e2
# Interwetten_s1
# Interwetten_sX
# Interwetten_s2
# Interwetten_e1
# Interwetten_eX
# Interwetten_e2
# 10BET_s1
# 10BET_sX
# 10BET_s2
# 10BET_e1
# 10BET_eX
# 10BET_e2
# 12bet_s1
# 12bet_sX
# 12bet_s2
# 12bet_e1
# 12bet_eX
# 12bet_e2
# 18Bet_s1
# 18Bet_sX
# 18Bet_s2
# 18Bet_e1
# 18Bet_eX
# 18Bet_e2
# Bwin_s1
# Bwin_sX
# Bwin_s2
# Bwin_e1
# Bwin_eX
# Bwin_e2
# Coral_s1
# Coral_sX
# Coral_s2
# Coral_e1
# Coral_eX
# Coral_e2
# Crown_s1
# Crown_sX
# Crown_s2
# Crown_e1
# Crown_eX
# Crown_e2
# Expekt_s1
# Expekt_sX
# Expekt_s2
# Expekt_e1
# Expekt_eX
# Expekt_e2
# Nordicbet_s1
# Nordicbet_sX
# Nordicbet_s2
# Nordicbet_e1
# Nordicbet_eX
# Nordicbet_e2
# Pinnacle_s1
# Pinnacle_sX
# Pinnacle_s2
# Pinnacle_e1
# Pinnacle_eX
# Pinnacle_e2
# SNAI_s1
# SNAI_sX
# SNAI_s2
# SNAI_e1
# SNAI_eX
# SNAI_e2
# STS_s1
# STS_sX
# STS_s2
# STS_e1
# STS_eX
# STS_e2
# Macauslot_s1
# Macauslot_sX
# Macauslot_s2
# Macauslot_e1
# Macauslot_eX
# Macauslot_e2
# Sportingbet_s1
# Sportingbet_sX
# Sportingbet_s2
# Sportingbet_e1
# Sportingbet_eX
# Sportingbet_e2
# 188bet_s1
# 188bet_sX
# 188bet_s2
# 188bet_e1
# 188bet_eX
# 188bet_e2
# Sbobet_s1
# Sbobet_sX
# Sbobet_s2
# Sbobet_e1
# Sbobet_eX
# Sbobet_e2
# Mansion88_s1
# Mansion88_sX
# Mansion88_s2
# Mansion88_e1
# Mansion88_eX
# Mansion88_e2
# Wewbet_s1
# Wewbet_sX
# Wewbet_s2
# Wewbet_e1
# Wewbet_eX
# Wewbet_e2
# Betfair_s1
# Betfair_sX
# Betfair_s2
# Betfair_e1
# Betfair_eX
# Betfair_e2
# Betsson_s1
# Betsson_sX
# Betsson_s2
# Betsson_e1
# Betsson_eX
# Betsson_e2
# Unibet_s1
# Unibet_sX
# Unibet_s2
# Unibet_e1
# Unibet_eX
# Unibet_e2
# 10x10bet_s1
# 10x10bet_sX
# 10x10bet_s2
# 10x10bet_e1
# 10x10bet_eX
# 10x10bet_e2
# 1xBet_s1
# 1xBet_sX
# 1xBet_s2
# 1xBet_e1
# 1xBet_eX
# 1xBet_e2
# 1xStavka_s1
# 1xStavka_sX
# 1xStavka_s2
# 1xStavka_e1
# 1xStavka_eX
# 1xStavka_e2
# 5Dimes_s1
# 5Dimes_sX
# 5Dimes_s2
# 5Dimes_e1
# 5Dimes_eX
# 5Dimes_e2
# 888Sport_s1
# 888Sport_sX
# 888Sport_s2
# 888Sport_e1
# 888Sport_eX
# 888Sport_e2


